package org.dromara.common.mybatis.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.google.common.base.CaseFormat;
import org.dromara.common.core.utils.sql.SqlUtil;
import org.dromara.common.mybatis.core.page.CustomConditions;

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.Arrays;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * QueryWrapper工具类
 */
public class QueryWrapperUtils {

    /**
     * 根据参数动态组装QueryWrapper
     *
     * @param customConditions filters {column: "userName", action: "==", value: "wenyin"} sorting userName DESC
     * @param <T>
     * @return
     */
    public static <T> QueryWrapper<T> structure(CustomConditions customConditions) {
        QueryWrapper<T> queryWrapper = new QueryWrapper<>();
        if (CollUtil.isEmpty(customConditions.getFilters())) {
            return queryWrapper;
        }
        for (CustomConditions.Condition condition : customConditions.getFilters()) {
            String column = condition.getColumn();
            if (condition.getAction() != null && !condition.getAction().isEmpty()) {
                if (condition.getAction().equals("isNull") || condition.getAction().equals("isNotNull") || condition.getAction().equals("isStr") || condition.getAction().equals("isNotStr")) {
                    // 这些不需要value值 直接拼条件
                    dealCondition(queryWrapper, condition, column);
                }
            }
            if (column == null || column.isEmpty() || condition.getValue() == null || condition.getValue().isEmpty() || condition.getAction() == null || condition.getAction().isEmpty()) {
                continue;
            }
            dealCondition(queryWrapper, condition, column);
        }
        return queryWrapper;
    }

    /**
     * 根据参数动态组装QueryWrapper
     *
     * @param customConditions  filters {column: "userName", action: "==", value: "wenyin"} sorting userName DESC
     * @param excludeConditions 忽略属性
     * @param <T>
     * @return
     */
    public static <T> QueryWrapper<T> structureFilter(CustomConditions customConditions, List<String> excludeConditions) {
        QueryWrapper<T> queryWrapper = new QueryWrapper<>();
        if (CollUtil.isEmpty(customConditions.getFilters())) {
            return queryWrapper;
        }
        for (CustomConditions.Condition condition : customConditions.getFilters()) {
            String column = condition.getColumn();
            if (excludeConditions != null && !excludeConditions.isEmpty()) {
                if (excludeConditions.contains(column)) {
                    continue;
                }
            }
            if (condition.getAction() != null && !condition.getAction().isEmpty()) {
                if (condition.getAction().equals("isNull") || condition.getAction().equals("isNotNull") || condition.getAction().equals("isStr") || condition.getAction().equals("isNotStr")) {
                    // 这些不需要value值 直接拼条件
                    dealCondition(queryWrapper, condition, column);
                }
            }
            if (column == null || column.isEmpty() || condition.getValue() == null || condition.getValue().isEmpty() || condition.getAction() == null || condition.getAction().isEmpty()) {
                continue;
            }
            dealCondition(queryWrapper, condition, column);
        }
        return queryWrapper;
    }


    private static <T> void dealCondition(QueryWrapper<T> queryWrapper, CustomConditions.Condition condition, String column) {
        //校验sql是否包含关键字
        SqlUtil.isSqlValid(column);
        column = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, column);
        switch (condition.getAction()) {
            case "=":
            case "==":
                queryWrapper.eq(column, condition.getValue());
                break;
            case "!=":
            case "<>":
                queryWrapper.ne(column, condition.getValue());
                break;
            case ">":
                queryWrapper.gt(column, condition.getValue());
                break;
            case "<":
                queryWrapper.lt(column, condition.getValue());
                break;
            case ">=":
                queryWrapper.ge(column, condition.getValue());
                break;
            case "<=":
                queryWrapper.le(column, condition.getValue());
                break;
            case "like":
                queryWrapper.like(column, condition.getValue());
                break;
            case "in":
                String[] strs = condition.getValue().split(",");
                queryWrapper.in(column, Arrays.asList(strs));
                break;
            case "notin":
                strs = condition.getValue().split(",");
                queryWrapper.notIn(column, Arrays.asList(strs));
                break;
            case "betweeen":
                strs = condition.getValue().split(",");
                if (strs.length != 2) {
                    break;
                }
                if (isNumeric(strs[0]) && isNumeric(strs[1])) {
                    LocalDateTime localDateTime1 = Instant.ofEpochMilli(Long.parseLong(strs[0])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                    LocalDateTime localDateTime2 = Instant.ofEpochMilli(Long.parseLong(strs[1])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                    queryWrapper.between(column, localDateTime1, localDateTime2);
                } else {
                    queryWrapper.between(column, strs[0], strs[1]);
                }

                break;
            case "isNull":
                queryWrapper.isNull(column);
                break;
            case "isNotNull":
                queryWrapper.isNotNull(column);
                break;
            case "isStr":
                queryWrapper.eq(column, "");//是空字符穿
                break;
            case "isNotStr":
                queryWrapper.ne(column, "");//不是空字符串
                break;
            default:
                break;
        }
    }

    /**
     * 生成主表查询sql语句
     *
     * @param tableName  主表表名
     * @param columns    主表属性
     * @param conditions 查询条件
     * @return
     */
    public static String buildMainSql(String tableName, List<String> columns, CustomConditions conditions) {
        StringBuilder mainSbf = new StringBuilder();
        //202408011934方法多添加一个AND deleted = 0系统不会自动拼接
        mainSbf.append(StrUtil.format("select id from {} where 1=1 AND deleted = 0 ", tableName));
        Set<String> columnSet = columns.stream().map(QueryWrapperUtils::camelToSnake).collect(Collectors.toSet());
        for (CustomConditions.Condition condition : conditions.getFilters()) {
            String column = camelToSnake(condition.getColumn());
            if (columnSet.contains(column)) {
                appendCondition(mainSbf, column, condition);
            }
        }
        return mainSbf.toString();
    }

    /**
     * 生成主表查询sql语句（增加主表过滤字段）
     *
     * @param tableName  主表表名
     * @param columns    主表属性
     * @param conditions 查询条件
     * @return
     */
    public static String buildMainSql(String tableName, List<String> columns, CustomConditions conditions, List<String> excludeMainConditions) {
        StringBuilder mainSbf = new StringBuilder();
        //202408011934方法多添加一个AND deleted = 0系统不会自动拼接
        mainSbf.append(StrUtil.format("select id from {} where 1=1 AND deleted = 0 ", tableName));
        Set<String> columnSet = columns.stream().map(QueryWrapperUtils::camelToSnake).collect(Collectors.toSet());
        for (CustomConditions.Condition condition : conditions.getFilters()) {
            String column = camelToSnake(condition.getColumn());
            // 增加主表过滤字段
            if (excludeMainConditions != null && excludeMainConditions.size() > 0) {
                if (excludeMainConditions.contains(column)) {
                    continue;
                }
            }
            if (columnSet.contains(column)) {
                appendCondition(mainSbf, column, condition);
            }
        }
        return mainSbf.toString();
    }


    public static void appendCondition(StringBuilder sb, String column, CustomConditions.Condition condition) {
        String action = condition.getAction();
        String value = condition.getValue();
        if ("accept_user_id".equals(column)) {
            sb.append(StrUtil.format(" AND ({} = '{}' OR {} = '' OR {} IS NULL)", column, value, column, column));
            return;
        }
        switch (action) {
            case "in":
                String inSql = Arrays.stream(value.split(",")).map(val -> "'" + val + "'").collect(Collectors.joining(","));
                sb.append(StrUtil.format(" AND `{}` IN ({}) ", column, inSql));
                break;
            case "notIn":
                String notInSql = Arrays.stream(value.split(",")).map(val -> "'" + val + "'").collect(Collectors.joining(","));
                sb.append(StrUtil.format(" AND `{}` NOT IN ({}) ", column, notInSql));
                break;
            case "=":
            case "==":
                sb.append(StrUtil.format(" AND `{}` = '{}' ", column, value));
                break;
            case "!=":
                sb.append(StrUtil.format(" AND `{}` != '{}' ", column, value));
                break;
            case "<>":
                sb.append(StrUtil.format(" AND `{}` <> '{}' ", column, value));
                break;
            case ">":
                sb.append(StrUtil.format(" AND `{}` > '{}' ", column, value));
                break;
            case "<":
                sb.append(StrUtil.format(" AND `{}` < '{}' ", column, value));
                break;
            case ">=":
                sb.append(StrUtil.format(" AND `{}` >= '{}' ", column, value));
                break;
            case "<=":
                sb.append(StrUtil.format(" AND `{}` <= '{}' ", column, value));
                break;
            case "like":
                sb.append(StrUtil.format(" AND `{}` LIKE '%{}%' ", column, value));
                break;
            case "isNull":
                sb.append(StrUtil.format(" AND `{}` IS NULL ", column));
                break;
            case "isNotNull":
                sb.append(StrUtil.format(" AND `{}` IS NOT NULL ", column));
                break;
            case "isStr":
                sb.append(StrUtil.format(" AND `{}` = '' ", column));
                break;
            case "isNotStr":
                sb.append(StrUtil.format(" AND `{}` != '' ", column));
                break;
            case "betweeen":
                String[] values = value.split(",");
                if (values.length != 2) {
                    break;
                }
                if (isNumeric(values[0]) && isNumeric(values[1])) {
                    LocalDateTime localDateTime1 = Instant.ofEpochMilli(Long.parseLong(values[0])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                    LocalDateTime localDateTime2 = Instant.ofEpochMilli(Long.parseLong(values[1])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                    sb.append(StrUtil.format(" AND `{}` BETWEEN '{}' AND '{}' ", column, localDateTime1, localDateTime2));
                } else {
                    sb.append(StrUtil.format(" AND `{}` BETWEEN '{}' AND '{}' ", column, values[0], values[1]));
                }
                break;
            default:
                break;
        }
    }

    //去掉字段的单引号 可以拼接自定义的sql方式，字段上不能有‘’
    public static void appendCondition2(StringBuilder sb, String column, CustomConditions.Condition condition) {
        String action = condition.getAction();
        String value = condition.getValue();
        if ("accept_user_id".equals(column)) {
            sb.append(StrUtil.format(" AND ({} = '{}' OR {} = '' OR {} IS NULL)", column, value, column, column));
            return;
        }
        switch (action) {
            case "in":
                String inSql = Arrays.stream(value.split(",")).map(val -> "'" + val + "'").collect(Collectors.joining(","));
                sb.append(StrUtil.format(" AND {} IN ({}) ", column, inSql));
                break;
            case "notIn":
                String notInSql = Arrays.stream(value.split(",")).map(val -> "'" + val + "'").collect(Collectors.joining(","));
                sb.append(StrUtil.format(" AND {} NOT IN ({}) ", column, notInSql));
                break;
            case "=":
            case "==":
                sb.append(StrUtil.format(" AND {} = '{}' ", column, value));
                break;
            case "!=":
                sb.append(StrUtil.format(" AND {} != '{}' ", column, value));
                break;
            case "<>":
                sb.append(StrUtil.format(" AND {} <> '{}' ", column, value));
                break;
            case ">":
                sb.append(StrUtil.format(" AND {} > '{}' ", column, value));
                break;
            case "<":
                sb.append(StrUtil.format(" AND {} < '{}' ", column, value));
                break;
            case ">=":
                sb.append(StrUtil.format(" AND {} >= '{}' ", column, value));
                break;
            case "<=":
                sb.append(StrUtil.format(" AND {} <= '{}' ", column, value));
                break;
            case "like":
                sb.append(StrUtil.format(" AND {} LIKE '%{}%' ", column, value));
                break;
            case "isNull":
                sb.append(StrUtil.format(" AND {} IS NULL ", column));
                break;
            case "isNotNull":
                sb.append(StrUtil.format(" AND {} IS NOT NULL ", column));
                break;
            case "isStr":
                sb.append(StrUtil.format(" AND {} = '' ", column));
                break;
            case "isNotStr":
                sb.append(StrUtil.format(" AND {} != '' ", column));
                break;
            case "betweeen":
                String[] values = value.split(",");
                if (values.length != 2) {
                    break;
                }
                if (isNumeric(values[0]) && isNumeric(values[1])) {
                    LocalDateTime localDateTime1 = Instant.ofEpochMilli(Long.parseLong(values[0])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                    LocalDateTime localDateTime2 = Instant.ofEpochMilli(Long.parseLong(values[1])).atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                    sb.append(StrUtil.format(" AND {} BETWEEN '{}' AND '{}' ", column, localDateTime1, localDateTime2));
                } else {
                    sb.append(StrUtil.format(" AND {} BETWEEN '{}' AND '{}' ", column, values[0], values[1]));
                }
                break;
            default:
                break;
        }
    }

    private static String camelToSnake(String str) {
        StringBuilder result = new StringBuilder();
        for (int i = 0; i < str.length(); i++) {
            char c = str.charAt(i);
            if (Character.isUpperCase(c)) {
                if (i > 0) {
                    result.append('_');
                }
                result.append(Character.toLowerCase(c));
            } else {
                result.append(c);
            }
        }
        return result.toString();
    }

    private static boolean isNumeric(String str) {
        return str != null && str.matches("\\d+");
    }

}
